from flask import Flask, render_template, redirect, url_for, request, session, jsonify
from datetime import datetime
from werkzeug.security import generate_password_hash, check_password_hash
import pymysql

app = Flask(__name__)
app.secret_key = 'your_secret_key'

# 数据库连接
db = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='forum'
)
cursor = db.cursor()


# 用户注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        age = request.form['age']
        password = request.form['password']
        email = request.form['email']
        hashed_password = generate_password_hash(password, method='sha256')
        cursor.execute("SELECT * FROM users WHERE username=%s", (username,))
        existing_user = cursor.fetchone()
        cursor.execute("SELECT * FROM users WHERE email=%s", (email,))
        existing_email = cursor.fetchone()
        # 在数据库中插入新用户
        if existing_user:
            return 'Existed username'
        if existing_email:
            return 'Existed email'
        else:
            sql = "INSERT INTO users (username, age, password, email, is_admin) VALUES (%s, %s, %s, %s, 0)"
            cursor.execute(sql, (username, age, hashed_password, email))
            db.commit()
        return redirect(url_for('login'))
    return render_template('_register.html')


# 用户登录
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        # 在数据库中查找用户
        sql = "SELECT * FROM users WHERE username = %s"
        cursor.execute(sql, (username,))
        user = cursor.fetchone()
        if user[5] == 1:
            if password == user[3]:
                return redirect(url_for('admin_list'))
            else:
                return 'Invalid username or password'
        else:
            if user and check_password_hash(user[3], password):
                session['user_id'] = user[0]
                session['username'] = user[1]
                return redirect(url_for('list'))
            else:
                return 'Invalid username or password'
    return render_template('_login.html')


@app.route('/logout')
def logout():
    # 在这里添加注销逻辑,例如清除session
    session.clear()
    return redirect(url_for('index'))


# 用户查看个人信息
@app.route('/detail')
def detail():
    user_id = session['user_id']
    # 从数据库中获取用户信息
    sql = "SELECT * FROM users WHERE user_id = %s"
    cursor.execute(sql, (user_id,))
    user = cursor.fetchone()
    sql = "SELECT * FROM posts WHERE author_id = %s"
    cursor.execute(sql, (user_id,))
    user_posts = cursor.fetchall()
    return render_template('_detail.html', user=user, user_posts=user_posts)


# 用户修改个人信息
@app.route('/detail/edit', methods=['GET', 'POST'])
def edit_detail():
    if 'user_id' in session:
        user_id = session['user_id']
        if request.method == 'POST':
            username = request.form['username']
            age = request.form['age']
            password = request.form['password']
            email = request.form['email']
            hashed_password = generate_password_hash(password, method='sha256')
            # 在数据库中更新用户信息
            sql = "UPDATE users SET username = %s, age = %s, password = %s, email = %s WHERE user_id = %s"
            cursor.execute(sql, (username, age, hashed_password, email, user_id))
            db.commit()
            return redirect(url_for('detail'))
        # 从数据库中获取用户信息
        sql = "SELECT * FROM users WHERE user_id = %s"
        cursor.execute(sql, (user_id,))
        user = cursor.fetchone()
        return render_template('_edit_detail.html', user=user)
    else:
        return redirect(url_for('login'))


# 用户查看空间
@app.route('/detail/observe/<int:user_id>', methods=['GET', 'POST'])
def in_detail(user_id):
    if user_id:
        if user_id == session['user_id']:
            return redirect(url_for('detail'))
        else:
            sql = "SELECT * FROM users WHERE user_id = %s"
            cursor.execute(sql, (user_id,))
            user = cursor.fetchone()
            sql = "SELECT * FROM posts WHERE author_id = %s"
            cursor.execute(sql, (user_id,))
            user_posts = cursor.fetchall()
            return render_template('_detail2.html', user=user, user_posts=user_posts)
    else:
        return 'User not found', 404


# 用户查看私信列表
@app.route('/message/<int:user_id>', )
def sender():
    sql = "SELECT DISTINCT user_id FROM messages, users WHERE recipient_id = user_id OR sender_id = user_id"
    cursor.execute(sql)
    all_senders = cursor.fetchall()
    return render_template('_senders.html', all_senders=all_senders)


# 用户查看与发送私信
@app.route('/message/send', methods=['GET', 'POST'])
def send_message(recipient_id):
    if 'user_id' in session:
        user_id = session['user_id']
        sql = "SELECT * FROM messages, users WHERE recipient_id = user_id AND sender_id = %s UNION SELECT * FROM messages, users WHERE recipient_id = %s AND sender_id = user_id ORDER BY m_time ASC"
        cursor.execute(sql, (user_id, user_id))
        all_messages = cursor.fetchall()
        if request.method == 'POST':
            current_time = datetime.now()
            time_str = current_time.strftime('%Y-%m-%d %H:%M:%S')
            content = request.form['content']
            sql = "INSERT INTO messages (sender_id, recipient_id, m_time, content) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (user_id, recipient_id, time_str, content))
            db.commit()
            return redirect(url_for('send_message', sender_id=recipient_id))
        return render_template('_send_message.html', all_messages=all_messages)
    else:
        return redirect(url_for('login'))


# 用户浏览帖子
@app.route('/list')
def list():
    # 从数据库中获取所有帖子
    sql = "SELECT * FROM posts, users WHERE author_id = user_id"
    cursor.execute(sql)
    all_posts = cursor.fetchall()
    return render_template('_list.html', all_posts=all_posts[::-1])


# 用户通过关键词搜索帖子
@app.route('/post/search', methods=['GET', 'POST'])
def search():
    if request.method == 'POST':
        keyword = request.form['keyword']
        # 从数据库中根据关键词搜索帖子
        sql = "SELECT * FROM posts, users WHERE (title LIKE %s OR content LIKE %s) AND user_id = author_id"
        cursor.execute(sql, (f'%{keyword}%', f'%{keyword}%'))
        posts = cursor.fetchall()
        return render_template('_search.html', posts=posts)
    else:
        return 'Keyword not found', 404


# 用户发表帖子
@app.route('/post/create', methods=['GET', 'POST'])
def editor():
    if 'user_id' in session:
        if request.method == 'POST':
            current_time = datetime.now()
            time_str = current_time.strftime('%Y-%m-%d %H:%M:%S')
            title = request.form['title']
            content = request.form['content']
            category = request.form['category']
            user_id = session['user_id']
            # 在数据库中插入新帖子
            sql = "INSERT INTO posts (title, p_time, content, category, author_id) VALUES (%s, %s, %s, %s, %s)"
            cursor.execute(sql, (title, time_str, content, category, user_id))
            db.commit()
            return redirect(url_for('list'))
        return render_template('_editor.html')
    else:
        return redirect(url_for('login'))


# 用户查看帖子
@app.route('/post/observe/<int:post_id>', methods=['GET', 'POST'])
def in_post(post_id):
    sql = "SELECT * FROM posts, users WHERE author_id = user_id"
    cursor.execute(sql)
    posts = cursor.fetchall()
    post = next((p for p in posts if p[0] == post_id), None)
    if post:
        sql = "SELECT * FROM replies, users, posts WHERE r_post_id = post_id AND replies.author_id = user_id"
        cursor.execute(sql)
        replies = cursor.fetchall()
        return render_template('_post.html', post=post, replies=replies)
    else:
        return 'Post not found', 404


# 用户在帖子下回复
@app.route('/post/<int:post_id>/reply', methods=['GET', 'POST'])
def reply(post_id):
    if 'user_id' in session:
        if request.method == 'POST':
            current_time = datetime.now()
            time_str = current_time.strftime('%Y-%m-%d %H:%M:%S')
            content = request.form['content']
            user_id = session['user_id']
            # 在数据库中插入新回复
            sql = "INSERT INTO replies (r_post_id, author_id, r_time, content) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (post_id, user_id, time_str, content))
            db.commit()
            return redirect(url_for('in_post', post_id=post_id))
        # 从数据库中获取帖子详情
        sql = "SELECT * FROM posts, users WHERE post_id = %s AND user_id = author_id"
        cursor.execute(sql, (post_id,))
        post = cursor.fetchone()
        return render_template('_reply.html', post=post)
    else:
        return redirect(url_for('login'))


# 用户查看板块
@app.route('/category/observe/<category>', methods=['GET', 'POST'])
def in_category(category):
    sql = "SELECT * FROM posts, users WHERE posts.category = %s AND posts.author_id = users.user_id"
    cursor.execute(sql, (category,))
    posts = cursor.fetchall()
    if posts:
        return render_template('_category.html', all_posts=posts[::-1])
    else:
        return 'Category not found', 404


# 管理员审核帖子内容并具有删除权限
@app.route('/admin/list')
def admin_list():
    # 从数据库中获取所有帖子
    sql = "SELECT * FROM posts, users WHERE author_id = user_id"
    cursor.execute(sql)
    all_posts = cursor.fetchall()
    return render_template('_admin_list.html', all_posts=all_posts)


@app.route('/admin/post/<int:post_id>')
def admin_post(post_id):
    # 从数据库中获取帖子下的所有回复
    sql = "SELECT * FROM posts, users WHERE author_id = user_id"
    cursor.execute(sql)
    posts = cursor.fetchall()
    post = next((p for p in posts if p[0] == post_id), None)
    sql = "SELECT * FROM replies, users, posts WHERE r_post_id = post_id AND replies.author_id = user_id"
    cursor.execute(sql)
    replies = cursor.fetchall()
    return render_template('_admin_post.html', post=post, replies=replies)


@app.route('/delete_post/<int:post_id>', methods=['POST'])
def delete_post(post_id):
    # 从数据库中删除帖子
    sql = "DELETE FROM replies WHERE r_post_id = %s"
    cursor.execute(sql, (post_id,))
    db.commit()
    sql = "DELETE FROM posts WHERE post_id = %s"
    cursor.execute(sql, (post_id,))
    db.commit()
    return redirect(url_for('admin_list'))


@app.route('/delete_reply/<int:reply_id>', methods=['POST'])
def delete_reply(reply_id):
    # 从数据库中删除回复
    sql = "DELETE FROM replies WHERE reply_id = %s"
    cursor.execute(sql, (reply_id,))
    db.commit()
    return redirect(url_for('admin_list'))


@app.route('/')
def index():
    return render_template('_index.html')


# 返回不存在页面的处理
@app.errorhandler(404)
def not_found(e):
    return render_template("404.html")


if __name__ == '__main__':
    app.run(debug=True)
